package pe.iifiis.rrhh.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

import org.apache.log4j.Logger;

import pe.iifiis.rrhh.beans.Empleado;
import pe.iifiis.rrhh.util.ConexionBDoracle;

public class EmpleadoDAO {
	Logger logger = Logger.getLogger("");

	public Empleado obtenerEmpleado(int idEmpleado){
		Empleado emp =new Empleado();
		Connection conn= null;
		Statement st=null;
		ResultSet rs=null;
		String sql= "SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER "+
		"FROM EMPLOYEES WHERE EMPLOYEE_ID= "+idEmpleado;
		
		try {
			ConexionBDoracle conOracle= new ConexionBDoracle();	
			conn =conOracle.obtenerConexionBD();
			st= conn.createStatement();
			rs= st.executeQuery(sql);
			if(rs.next()){
				emp.setIdEmpleado(rs.getInt(1));
				//emp.setIdEmpleado(rs.getInt(1));
				emp.setNombre(rs.getString(2));
				emp.setApellidos(rs.getString(3));
				emp.setEmail(rs.getString(4));
				emp.setTelefono(rs.getString(5));			}
		}catch(Exception e){
			e.printStackTrace();
			
		}finally{
			try{
				if(rs!=null)rs.close();
				if(st!=null)st.close();
				if(conn!=null)conn.close();
				
				
			}catch(Exception e){e.printStackTrace();}
		}
		return  emp;
		
	}
	//*************metodo busqueda de empleado********* //
	public ArrayList<Empleado> buscarEmpleado(String consulta){
		logger.info("******inicio de EmpleadoAOD en metodo busqueda de empleado*****");
		ArrayList<Empleado> listabusqueda = new ArrayList<Empleado>();
		
		Connection conn= null;
		Statement st=null;
		ResultSet rs=null;
		String sqlbuscar= "SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER "+
		"FROM EMPLOYEES +" +
			"WHERE FIRST_NAME LIKE '%"+consulta+"%'+" +
			"WHERE LAST_NAME LIKE '%"+consulta+"%'+";
		
		try {
			ConexionBDoracle conOracle= new ConexionBDoracle();	
			conn =conOracle.obtenerConexionBD();
			st= conn.createStatement();
			rs= st.executeQuery(sqlbuscar);
			logger.info(sqlbuscar);
			while(rs.next()){
				Empleado emp= new Empleado();
				emp.setIdEmpleado(rs.getInt(1));
				//emp.setIdEmpleado(rs.getInt(1));
				emp.setNombre(rs.getString(2));
				emp.setApellidos(rs.getString(3));
				emp.setEmail(rs.getString(4));
				emp.setTelefono(rs.getString(5));	
				listabusqueda.add(emp);
			}
		}catch(Exception e){
			e.printStackTrace();
			logger.error("erroren la ejecucion"+e);
			
		}finally{
			try{
				if(rs!=null)rs.close();
				if(st!=null)st.close();
				if(conn!=null)conn.close();
				
				
			}catch(Exception e2){e2.printStackTrace();}
		}
		logger.info("Fin del metodo insertar empleado");
		return  listabusqueda;
		 
	}
	//insertar Empleado//
	public int insertarEmpleado( int idEmpleado){
		logger.info("******inicio de EmpleadoAOD en metodo insertar de empleado*****");
		int resultado=0;
		Empleado emp= new Empleado();
		
		Connection conn= null;
		Statement st=null;
		String sqlInsert= "INSERT INTO EMPLOYEES +" +
				"EMPLOYEE_ID," +
				" FIRST_NAME, +" +
				"LAST_NAME+" +
				", EMAIL,+" +
				" PHONE_NUMBER +" +
				"JOD_ID+" +
				"SALARY+" +
				"ID_MANAGER"+
				"VALUES("+
				"EMPLOYEES_SEQ.NETXVAL,"+
				"'"+emp.getIdEmpleado()+","+
				"'"+emp.getNombre()+","+
				"'"+emp.getApellidos()+","+
				"'"+emp.getEmail()+",";
		
		try {
			ConexionBDoracle conOracle= new ConexionBDoracle();	
			conn =conOracle.obtenerConexionBD();
			st= conn.createStatement();
			resultado= st.executeUpdate(sqlInsert);
			
		}catch(Exception e){
			e.printStackTrace();
			logger.error("error en la ejecucion"+e);
			
		}finally{
			try{
				if(st!=null)st.close();
				if(conn!=null)conn.close();
				
				
			}catch(Exception e2){e2.printStackTrace();}
		}
		return  resultado;
}
	public int eliminarrEmpleado( int idEmpleado){
		logger.info("******inicio de EmpleadoAOD en metodo eliminar de empleado*****");
		int resultado=0;
		Empleado emp= new Empleado();
		
		Connection conn= null;
		Statement st=null;
		String sqlDelete= "DELETE FROM  EMPLOYEES +" +
				"EMPLOYEE_ID," +
				" FIRST_NAME, +" +
				"LAST_NAME+" +
				", EMAIL,+" +
				" PHONE_NUMBER +" +
				"JOD_ID+" +
				"SALARY+" +
				"ID_MANAGER"+
				"VALUES("+
				"EMPLOYEES_SEQ.NETXVAL,+" +
				"WHERE EMPLOYEE_ID= "+idEmpleado;
		
		try {
			ConexionBDoracle conOracle= new ConexionBDoracle();	
			conn =conOracle.obtenerConexionBD();
			st= conn.createStatement();
			resultado= st.executeUpdate(sqlDelete);
			
		}catch(Exception e){
			e.printStackTrace();
			logger.error("error en la ejecucion"+e);
			
		}finally{
			try{
				if(st!=null)st.close();
				if(conn!=null)conn.close();
				
				
			}catch(Exception e2){e2.printStackTrace();}
		}
		return  resultado;
}
	}
